package de.bastian.hinterleitner.database;

import java.util.ArrayList;
import java.util.Date;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.graphics.drawable.Drawable;
import de.bastian.hinterleitner.tools.Category;
import de.bastian.hinterleitner.tools.DataBaseEntry;
import de.bastian.hinterleitner.tools.Entry;
import de.bastian.hinterleitner.tools.ErrorLog;
import de.bastian.hinterleitner.tools.Unit;
import de.hijacksoft.wyc.R;

public abstract class DataHelper {

	/**
	 * Log TAG
	 */
	protected static final String TAG = "DataHelper";
	/**
	 * SQL Table Tags
	 */
	protected static final String TAG_ID = "id";
	protected static final String TAG_NAME = "name";
	protected static final String TAG_VALUE = "value";
	protected static final String TAG_YEAR = "year";
	protected static final String TAG_MONTH = "month";
	protected static final String TAG_DAY = "day";
	protected static final String TAG_CATEGORY = "category";
	protected static final String TAG_DRAWABLE = "drawable";
	protected static final String TAG_REFERENCE = "reference";
	protected static final String TAG_CURRENCY = "currency";
	/**
	 * SQL Database Tags
	 */
	protected static final String DATABASE_NAME = "database.db";
	protected static final int DATABASE_VERSION = 4;
	protected static final String TABLE_NAME = "table1";
	protected static final String TABLE_CATEGORY = "table2";
	/**
	 * Singelton representing the DataHelper that is in use
	 */
	private static DataHelper singleton;

	/**
	 * all entries in the database
	 */
	protected ArrayList<Entry> entries;

	/**
	 * default icon to use
	 */
	protected static final String icon = "trash";

	protected Context context;

	protected static boolean update = false;

	public static final int MODE_OFFLINE = 0;
	public static final int MODE_SYNCHRONIZED = 1;
	public static final int MODE_ONLINE = 2;

	public static int mode = MODE_OFFLINE;

	/**
	 * creates or returns a singleton DataHelper
	 * 
	 * @param context
	 *            an Application Context
	 * @return the DataHelper
	 */
	public static DataHelper getDataHelper(Context context) {
		if (singleton == null) {
			switch (mode) {
			case MODE_OFFLINE:
				singleton = new OfflineHelper(context);
				break;
			default:
				singleton = new OnlineHelper(context);
			}
			singleton.context = context;
			if (singleton.getAllCategories() != null
					&& singleton.getAllCategories().size() == 0) {
				singleton.createSampleCategories();
			}
			if (update) {
				singleton.getAllCategories();
				update = false;
			}
		} else {
			singleton.context = context;
			singleton.onResume();
		}

		return singleton;
	}

	public void init() {
		entries = getAllEntries();
	}

	public abstract void onResume();

	/**
	 * return whether the database is preloaded into an intern arraylist
	 * 
	 * @return
	 */
	public boolean isPreloaded() {
		return (entries != null);
	}

	protected void createSampleCategories() {
		addTrashCategory();
		String[] names = context.getResources().getStringArray(
				R.array.samplecategories);
		String[] icons = context.getResources().getStringArray(
				R.array.sampleicons);
		for (int i = 0; i < names.length && i < icons.length; i++) {
			addCategory(new Category(names[i], icons[i], -1));
		}
	}

	/**
	 * protected Constructor. Use getDataHelper(Context context) to get a
	 * singleton of this class
	 * 
	 * @param context
	 */
	protected DataHelper(Context context) {
		this.context = context;
	}

	public abstract boolean close();

	/**
	 * adds an Entry to your Database
	 * 
	 * @param eintrag
	 *            the Entry to add
	 */
	public abstract void insert(Entry eintrag);

	/**
	 * takes an ArrayList and creates a new Database from it
	 * 
	 * @param list
	 */
	public void createDBfromList(ArrayList<DataBaseEntry> list) {
		deleteAll();
		singleton.addTrashCategory();
		for (int i = 0; i < list.size(); i++) {
			DataBaseEntry e = list.get(i);
			if (e instanceof Entry) {
				insert((Entry) e);
			} else if (e instanceof Category) {
				Category c = (Category) e;
				singleton.addCategory(c);
			}
		}
		update = true; // to take categories from the entries, not from the
					   // category-db
		ArrayList<Category> categories = singleton.getAllCategories();
		update = false;
	}

	/**
	 * Updates an Entry
	 * 
	 * @param eintrag
	 */
	public abstract void update(Entry eintrag);

	protected Entry getById(int id) {
		if (entries == null)
			return null;
		for (int i = 0; i < entries.size(); i++) {
			if (entries.get(i).getId() == id)
				return entries.get(i);
		}
		return null;
	}

	/**
	 * deletes the whole Table
	 */
	public abstract void deleteAll();

	/**
	 * Deletes one Entry
	 * 
	 * @param eintrag
	 */
	public void delete(Entry eintrag) {
		if (entries != null)
			if (!entries.remove(eintrag)) {
				for (Entry e : entries) {
					if (e.getId() == eintrag.getId())
						entries.remove(e);
					break;
				}
			}
		delete(eintrag.getId());
	}

	/**
	 * Deletes one Entry by its ID
	 * 
	 * @param id
	 */
	public abstract void delete(int id);

	/**
	 * gets an Entry of your database by the id
	 * 
	 * @param id
	 * @return
	 */
	public abstract Entry getEntryById(int id);

	protected ArrayList<Entry> getEntriesFromCursor(Cursor cursor) {
		ArrayList<Entry> list = new ArrayList<Entry>();
		if (cursor.moveToFirst()) {
			do {
				try {
					Entry entry = new Entry(cursor.getString(1),
							cursor.getDouble(2), cursor.getInt(0));
					entry.setDate(cursor.getInt(3), cursor.getInt(4),
							cursor.getInt(5));
					entry.setRepeatableReference(cursor.getInt(7));
					entry.setCategory(new Category(cursor.getString(6),
							getCategoryDrawableName(cursor.getString(6)),
							getCategoryID(cursor.getString(6))));
					entry.setUnit(Unit.get(cursor.getString(8), context));
					list.add(0, entry);
				} catch (android.database.CursorIndexOutOfBoundsException ex) {
					ErrorLog.write(context,
							"cursor out of bounds (DataHelper:getEntriesFromCursor)");
				}
			} while (cursor.moveToNext());
		}
		return list;
	}

	/**
	 * gets a list of all entries stored in the database
	 * 
	 * @return
	 */
	public abstract ArrayList<Entry> getAllEntries();

	/**
	 * used to get the number of all entries in the database (quite) fast
	 * 
	 * @return the number of entries
	 */
	public abstract int getNumEntries();

	/**
	 * gets a list of entries stored in the database
	 * 
	 * @return
	 */
	public abstract ArrayList<Entry> getAllEntriesAt(int year, int month,
			int day);

	/**
	 * gets a list of entries stored in the database from the real database
	 * 
	 * @return
	 */
	protected abstract ArrayList<Entry> getFromDBAllEntriesAt(int year,
			int month, int day);

	/**
	 * gets a list of entries stored in the database with the name containing a
	 * given substring
	 * 
	 * @param substring
	 *            the substring in the itemname to look for
	 * @return the list containing all entries with that substring in the name
	 */
	public abstract ArrayList<Entry> getAllEntriesContaining(String substring);

	/**
	 * gets a list of entries stored in the database
	 * 
	 * @return
	 */
	public abstract ArrayList<Entry> getAllEntriesAt(int year, int month);

	/**
	 * gets a list of entries stored in the database
	 * 
	 * @return
	 */
	public abstract ArrayList<Entry> getAllEntriesAt(int year);

	/**
	 * gets a list of entries stored in the database
	 * 
	 * @return
	 */
	public abstract ArrayList<Entry> getAllEntriesByCategory(Category c);

	/**
	 * renames a whole category by renaming the single category tags of each
	 * item in that category
	 * 
	 * @param c
	 *            the old category
	 * @param newName
	 *            the new name for the category
	 */
	public abstract boolean renameCategory(Category c, String newName);

	/**
	 * deletes a whole category by renaming the single category tags of each
	 * item to ""
	 * 
	 * @param category
	 *            the category to delete
	 */
	public abstract boolean deleteCategory(Category category);

	/**
	 * returns the complete money spent/gained in one category
	 * 
	 * @param cat
	 *            the category to check
	 * @return a double containing the transaction value
	 */
	public double getValueOfCategory(Category cat) {
		double val = 0;
		if (entries != null) {
			for (Entry e : entries) {
				if (e.getCategory().equals(cat)) {
					val += e.getValueAs(context.getString(R.string.currency),
							context);
				}
			}
			return val;
		}
		for (Entry e : getAllEntriesByCategory(cat)) {
			val += e.getValueAs(context.getString(R.string.currency), context);
		}
		return val;
	}

	/**
	 * gets a list of all categories stored in the database
	 * 
	 * @return
	 */
	public abstract ArrayList<Category> getAllCategories();

	/**
	 * Used to get all distinct names of entries existing in the database
	 * 
	 * @return a list of all names
	 */
	public abstract ArrayList<String> getAllEntryNames();

	/**
	 * gets a list of all years stored in the database
	 * 
	 * @return
	 */
	public abstract ArrayList<Integer> getAllYears();

	/**
	 * gets a list of all months stored in the database
	 * 
	 * @return
	 */
	public abstract ArrayList<Integer> getAllMonths(int year);

	/**
	 * gets a list of all days stored in the database
	 * 
	 * @return
	 */
	public abstract ArrayList<Integer> getAllDays(int year, int month);

	/**
	 * used to get the drawable corresponding to the category
	 * 
	 * @param category
	 *            the name of the category
	 * @return the used drawable
	 */
	public abstract Drawable getCategoryDrawable(String category);

	/**
	 * gets the drawable referring to the given category name
	 * 
	 * @param category
	 *            the name of the category
	 * @return the drawable icon
	 */
	public abstract String getCategoryDrawableName(String category);

	/**
	 * gets the ID referring to the given category name
	 * 
	 * @param category
	 *            the name of the category
	 * @return the ID
	 */
	public abstract int getCategoryID(String category);

	/**
	 * sets the drawable icon of a category
	 * 
	 * @param cat
	 *            the name of the category
	 * @param drawable
	 *            the drawable to be set as icon
	 */
	public abstract void setCategoryDrawable(String cat, String drawable);

	/**
	 * adds a new category to the database
	 * 
	 * @param cat
	 *            the category
	 * @return
	 */
	public abstract boolean addCategory(Category cat);

	protected abstract void addTrashCategory();

	public double getValueOfCategory(Category cat, ArrayList<Entry> list) {
		double val = 0;
		for (int i = 0; i < list.size(); i++) {
			if (list.get(i).getCategory()
					.equals(new Category(cat.name, cat.icon, cat.id))) {
				val += list.get(i).getValueAs(
						context.getString(R.string.currency), context);
			}
		}
		return val;
	}

	public void addRepeatable(Entry start, long period, long end) {
		
		// 1900 bug
		Date endDate = new Date(end);
		endDate.setYear(endDate.getYear()-1900);
		end = endDate.getTime();
		
		start.setRepeatableReference(getNewRefID());
		insert(start);
		long time = start.getDate().getTime();
		while (time <= end) {
			time = time + period;
			start = new Entry(start);
			start.setDate(new Date(time));
			insert(start);
		}
	}

	public void addRepeatable(Entry start, int years, int months, int days,
			long end) {

		// 1900 bug
		Date endDate = new Date(end);
		endDate.setYear(endDate.getYear()-1900);
		end = endDate.getTime();

		boolean wrongTime = (years <= 0) && (months <= 0) && (days <= 0);
		if (start.getDate().getTime() >= end || wrongTime) {
			insert(start);
		} else {
			start.setRepeatableReference(getNewRefID());
			while (start.getDate().getTime() <= end) {
				insert(start);
				Date date = start.getDate();
				start = new Entry(start, -1);
				date.setYear(date.getYear() + years);
				date.setMonth(date.getMonth() + months);
				date.setDate(date.getDate() + days);
				start.setDate(date);
			}
		}
	}

	public void updateRepeatable(Entry start, int years, int months, int days,
			long end) {
		ArrayList<Entry> oldOnes = getAllRepeatables(
				start.getRepeatableReference(), start.getDate());
		for (int i = 0; i < oldOnes.size(); i++) {
			delete(oldOnes.get(i));
		}
		addRepeatable(start, years, months, days, end);
	}

	public Entry getLatestRepeatable(int repeatableReference) {
		ArrayList<Entry> repeatables = getAllRepeatables(repeatableReference,
				new Date(0));
		Entry result = null;
		for (int i = 0; i < repeatables.size(); i++) {
			if (result == null) {
				result = repeatables.get(i);
			} else if (repeatables.get(i).getDate().getTime() > result
					.getDate().getTime()) {
				result = repeatables.get(i);
			}
		}
		return result;
	}

	protected abstract ArrayList<Entry> getAllRepeatables(
			int repeatableReference, Date date);

	public ArrayList<Entry> getAllRepeatables(int repeatableReference) {
		return getAllRepeatables(repeatableReference, new Date(0l));
	}

	protected abstract int getNewRefID();

	public void deleteAllRepeatablesSince(Entry editedItem) {
		ArrayList<Entry> entries = getAllRepeatables(
				editedItem.getRepeatableReference(), editedItem.getDate());
		for (int i = 0; i < entries.size(); i++) {
			delete(entries.get(i));
		}
	}

	public abstract ArrayList<Entry> getAllEntriesGroupedByName();

	protected boolean containsEntriesWith(String name, ArrayList<Entry> list) {
		for (int i = 0; i < list.size(); i++) {
			if (list.get(i).getName().equals(name)) {
				return true;
			}
		}
		return false;
	}

	public abstract Category getCategoryByName(String name);

	// OPENHELPER
	// -----------------------------------------------------------------------------------

	/**
	 * @author Basti
	 */
	protected static class OpenHelper extends SQLiteOpenHelper {

		protected Context context;

		OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			this.context = context;
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL("CREATE TABLE " + TABLE_NAME + "(" + TAG_ID
					+ " INTEGER PRIMARY KEY, " + TAG_NAME + " TEXT, "
					+ TAG_VALUE + " DOUBLE, " + TAG_YEAR + " INTEGER, "
					+ TAG_MONTH + " INTEGER, " + TAG_DAY + " INTEGER, "
					+ TAG_CATEGORY + " TEXT, " + TAG_REFERENCE + " INTEGER, "
					+ TAG_CURRENCY + " TEXT NOT NULL DEFAULT '"
					+ context.getString(R.string.currency) + "')");
			db.execSQL("CREATE TABLE " + TABLE_CATEGORY + "(" + TAG_ID
					+ " INTEGER PRIMARY KEY, " + TAG_NAME + " TEXT, "
					+ TAG_DRAWABLE + " TEXT)");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			if (oldVersion < 2 && newVersion >= 2) { // added categories
				db.execSQL("CREATE TABLE " + TABLE_CATEGORY + "(" + TAG_ID
						+ " INTEGER PRIMARY KEY, " + TAG_NAME + " TEXT, "
						+ TAG_DRAWABLE + " TEXT)");
				update = true;
			}
			if (oldVersion < 3 && newVersion >= 3) { // added repeatables
				String query = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN "
						+ TAG_REFERENCE + " INTEGER NOT NULL DEFAULT -1";
				db.execSQL(query);
			}
			if (oldVersion < 4 && newVersion >= 4) { // added currencies
				String query = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN "
						+ TAG_CURRENCY + " TEXT NOT NULL DEFAULT '"
						+ context.getString(R.string.currency) + "'";
				db.execSQL(query);
			}
		}
	}

}
